﻿using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;

using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq.Expressions;
using Microsoft.Phone.Data.Linq.Mapping;

using System.Linq;
using System.Collections.Specialized;

using System.ComponentModel;
using System.Collections.ObjectModel;
using System.Collections.Generic;

namespace OneByOneSolitaire
{
    public class DataBase : DataContext
    {
         // Specify the connection string as a static, used in main page and app.xaml.
        public static string DBConnectionString = "Data Source=isostore:/OneByOne.sdf";

        // Pass the connection string to the base class.
        public DataBase(string connectionString)
            : base(connectionString)
        { 
            // debug
            // deleteAllData();
        }

        private void deleteAllData() {

            try
            {
                foreach (Move move in moves)
                {
                    moves.DeleteOnSubmit(move);
                }
                SubmitChanges();
                foreach (Game game in games)
                {
                    games.DeleteOnSubmit(game);
                }
                SubmitChanges();
                foreach (Player player in players)
                {
                    players.DeleteOnSubmit(player);
                }
                SubmitChanges();
            }
            catch (Exception ex) {
                m_lastError = ex.Message;
            }
        }

        // Specify a single table for the to-do items.
        public Table<Player> players;
        public Table<Game> games;
        public Table<Move> moves;

        private string m_lastError = "";
        
        public int getPreviousMoveId(int moveId, int gameId)
        {
            try
            {
                int count = (from c in moves
                             where c.mv_id < moveId && c.gm_id == gameId
                             select c.mv_id).Count();
                if (count > 0)
                {
                    int mv_id = (from c in moves
                                 where c.mv_id < moveId && c.gm_id == gameId
                                 select c.mv_id).Max();
                    return mv_id;
                }
                else
                    return 0;
            }
            catch (Exception ex) {
                m_lastError = ex.Message;
                return 0;
            }
        }

        public int getNextMoveId(int moveId, int gameId) 
        {
            try
            {
                int count = (from c in moves
                             where c.mv_id > moveId && c.gm_id == gameId
                             select c.mv_id).Count();
                if (count > 0)
                {
                    int mv_id = (from c in moves
                                 where c.mv_id > moveId && c.gm_id == gameId
                                 select c.mv_id).Min();
                    return mv_id;
                }
                else
                    return 0;
            }
            catch (Exception ex) {
                m_lastError = ex.Message;
                return 0;
            }
        }

        public Move getMove(int moveId, int gameId) 
        {
            try {
                int count = (from c in moves
                             where c.mv_id == moveId && c.gm_id == gameId
                             select c).Count();
                if (count > 0)
                {
                    Move move = (from c in moves
                                 where c.mv_id == moveId && c.gm_id == gameId
                                 select c).First();
                    return move;
                }
                else 
                    return null;
            }
            catch (Exception ex)
            {
                m_lastError = ex.Message;
                return null;
            }
        }

        public Player getPlayer() 
        {
            try
            {
                Player player = (from c in players
                                 where c.pl_activo == true
                                 select c).First();
                return player;
            }
            catch (Exception ex) {
                m_lastError = ex.Message;
                return null;            
            }
        }

        public Player createPlayer(string name) 
        {
            try
            {
                foreach (Player i in players) {
                    i.pl_activo = false;
                }

                Player player = new Player();
                player.pl_name = name;
                player.pl_activo = true;
                players.InsertOnSubmit(player);

                SubmitChanges();
                
                return player;
            }
            catch (Exception ex)
            {
                m_lastError = ex.Message;
                return null;
            }
        }

        public Game createGame(string name, int number, int playerId)
        {
            try
            {
                Game game = new Game();
                game.gm_name = name;
                game.gm_number = number;
                game.pl_id = playerId;
                games.InsertOnSubmit(game);
                SubmitChanges();
                return game;
            }
            catch (Exception ex)
            {
                m_lastError = ex.Message;
                return null;
            }
        }

        public int getLastGameNumber(int playerId) 
        {
            try {
                int count = (from c in games
                             where c.pl_id == playerId
                             select c.gm_number).Count();

                if (count > 0)
                {
                    int gm_number = (from c in games
                                     where c.pl_id == playerId
                                     select c.gm_number).Max();
                    return gm_number;
                }
                else
                    return 0;
            }
            catch (Exception ex) {
                m_lastError = ex.Message;
                return 0;
            }
        }

        public List<Game> getGamesForPlayer(int playerId) 
        {
            List<Game> games = new List<Game>();

            var gamesForPlayer =
                from c in this.games
                where c.pl_id == playerId
                select c;

            foreach (var game in gamesForPlayer)
            {
                games.Add(game);
            }

            return games;
        }

        public Move createMove(int x1, int y1, int x2, int y2, int gameId) 
        {
            try
            {
                Move move = new Move();
                move.mv_x1 = x1;
                move.mv_x2 = x2;
                move.mv_y1 = y1;
                move.mv_y2 = y2;
                move.gm_id = gameId;
                moves.InsertOnSubmit(move);
                SubmitChanges();
                return move;
            }
            catch (Exception ex)
            {
                m_lastError = ex.Message;
                return null;
            }
        }

        public bool deleteMoves(int moveId, int gameId) 
        {
            var movesToDelete =
                from c in moves 
                where c.mv_id > moveId && c.gm_id > gameId
                select c;

            foreach (var move in movesToDelete)
            {
                moves.DeleteOnSubmit(move);
            }

            try
            {
                SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                m_lastError = ex.Message;
                return false;
            }            
        }

        public string getLastErrorMsg() 
        {
            return m_lastError;
        }

    }

    [Table]
    public class Player : INotifyPropertyChanged, INotifyPropertyChanging
    {
        // Define ID: private field, public property and database column.
        private int m_pl_id;

        [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
        public int pl_id
        {
            get
            {
                return m_pl_id;
            }
            set
            {
                if (m_pl_id != value)
                {
                    NotifyPropertyChanging("pl_id");
                    m_pl_id = value;
                    NotifyPropertyChanged("pl_id");
                }
            }
        }
        // Define item name: private field, public property and database column.
        private string m_pl_name;

        [Column]
        public string pl_name
        {
            get
            {
                return m_pl_name;
            }
            set
            {
                if (m_pl_name != value)
                {
                    NotifyPropertyChanging("pl_name");
                    m_pl_name = value;
                    NotifyPropertyChanged("pl_name");
                }
            }
        }

        // Define completion value: private field, public property and database column.
        private bool m_pl_activo;

        [Column]
        public bool pl_activo
        {
            get
            {
                return m_pl_activo;
            }
            set
            {
                if (m_pl_activo != value)
                {
                    NotifyPropertyChanging("pl_activo");
                    m_pl_activo = value;
                    NotifyPropertyChanged("pl_activo");
                }
            }
        }
        // Version column aids update performance.
        [Column(IsVersion = true)]
        private Binary _version;

        // Define the entity set for the collection side of the relationship.
        private EntitySet<Game> m_games;

        [Association(Storage = "m_games", OtherKey = "pl_id", ThisKey = "pl_id")]
        public EntitySet<Game> games
        {
            get { return this.m_games; }
            set { this.m_games.Assign(value); }
        }

        // Assign handlers for the add and remove operations, respectively.
        public Player()
        {
            m_games = new EntitySet<Game>(
                new Action<Game>(this.attach_game),
                new Action<Game>(this.detach_game)
                );
        }

        // Called during an add operation
        private void attach_game(Game game)
        {
            NotifyPropertyChanging("Game");
            game.player = this;
        }

        // Called during a remove operation
        private void detach_game(Game game)
        {
            NotifyPropertyChanging("Game");
            game.player = null;
        }

        public override string ToString() 
        {
            return m_pl_name;
        }

        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged;

        // Used to notify the page that a data context property changed
        private void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }

        #endregion

        #region INotifyPropertyChanging Members

        public event PropertyChangingEventHandler PropertyChanging;

        // Used to notify the data context that a data context property is about to change
        private void NotifyPropertyChanging(string propertyName)
        {
            if (PropertyChanging != null)
            {
                PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
            }
        }

        #endregion
    }

    [Table]
    public class Game : INotifyPropertyChanged, INotifyPropertyChanging
    {
        // Define ID: private field, public property and database column.
        private int m_gm_id;

        [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
        public int gm_id
        {
            get
            {
                return m_gm_id;
            }
            set
            {
                if (m_gm_id != value)
                {
                    NotifyPropertyChanging("gm_id");
                    m_gm_id = value;
                    NotifyPropertyChanged("gm_id");
                }
            }
        }
        // Define item name: private field, public property and database column.
        private string m_gm_name;

        [Column]
        public string gm_name
        {
            get
            {
                return m_gm_name;
            }
            set
            {
                if (m_gm_name != value)
                {
                    NotifyPropertyChanging("gm_name");
                    m_gm_name = value;
                    NotifyPropertyChanged("gm_name");
                }
            }
        }

        // Define completion value: private field, public property and database column.
        private int m_gm_number;

        [Column]
        public int gm_number
        {
            get
            {
                return m_gm_number;
            }
            set
            {
                if (m_gm_number != value)
                {
                    NotifyPropertyChanging("gm_number");
                    m_gm_number = value;
                    NotifyPropertyChanged("gm_number");
                }
            }
        }
        // Version column aids update performance.
        [Column(IsVersion = true)]
        private Binary _version;

        // Foreign Key column for the associated Player ID value
        private int m_pl_id;

        [Column]
        public int pl_id
        {
            get
            {
                return m_pl_id;
            }
            set
            {
                if (m_pl_id != value)
                {
                    NotifyPropertyChanging("pl_id");
                    m_pl_id = value;
                    NotifyPropertyChanged("pl_id");
                }
            }
        }

        // Entity reference, to identify the Player "storage" table
        private EntityRef<Player> m_player;

        // Association, to describe the relationship between this key and that "storage" table
        [Association(Storage = "m_player", ThisKey = "pl_id", OtherKey = "pl_id", IsForeignKey = true)]
        public Player player
        {
            get { return m_player.Entity; }
            set
            {
                NotifyPropertyChanging("player");
                m_player.Entity = value;

                if (value != null)
                {
                    m_pl_id = value.pl_id;
                }

                NotifyPropertyChanging("player");
            }
        }

        // Define the entity set for the collection side of the relationship.
        private EntitySet<Move> m_moves;

        [Association(Storage = "m_moves", OtherKey = "gm_id", ThisKey = "gm_id")]
        public EntitySet<Move> moves
        {
            get { return this.m_moves; }
            set { this.m_moves.Assign(value); }
        }

        // Assign handlers for the add and remove operations, respectively.
        public Game()
        {
            m_moves = new EntitySet<Move>(
                new Action<Move>(this.attach_move),
                new Action<Move>(this.detach_move)
                );
        }

        // Called during an add operation
        private void attach_move(Move move)
        {
            NotifyPropertyChanging("Move");
            move.game = this;
        }

        // Called during a remove operation
        private void detach_move(Move move)
        {
            NotifyPropertyChanging("Move");
            move.game = null;
        }

        public override string ToString()
        {
            return m_gm_name;
        }

        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged;

        // Used to notify the page that a data context property changed
        private void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }

        #endregion

        #region INotifyPropertyChanging Members

        public event PropertyChangingEventHandler PropertyChanging;

        // Used to notify the data context that a data context property is about to change
        private void NotifyPropertyChanging(string propertyName)
        {
            if (PropertyChanging != null)
            {
                PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
            }
        }

        #endregion
    }

    [Table]
    public class Move : INotifyPropertyChanged, INotifyPropertyChanging
    {
        // Define ID: private field, public property and database column.
        private int m_mv_id;

        [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
        public int mv_id
        {
            get
            {
                return m_mv_id;
            }
            set
            {
                if (m_mv_id != value)
                {
                    NotifyPropertyChanging("mv_id");
                    m_mv_id = value;
                    NotifyPropertyChanged("mv_id");
                }
            }
        }
        
        // Define item x1: private field, public property and database column.
        private int m_mv_x1;

        [Column]
        public int mv_x1
        {
            get
            {
                return m_mv_x1;
            }
            set
            {
                if (m_mv_x1 != value)
                {
                    NotifyPropertyChanging("mv_x1");
                    m_mv_x1 = value;
                    NotifyPropertyChanged("mv_x1");
                }
            }
        }

        // Define item x2: private field, public property and database column.
        private int m_mv_x2;

        [Column]
        public int mv_x2
        {
            get
            {
                return m_mv_x2;
            }
            set
            {
                if (m_mv_x2 != value)
                {
                    NotifyPropertyChanging("mv_x2");
                    m_mv_x2 = value;
                    NotifyPropertyChanged("mv_x2");
                }
            }
        }

        // Define item y1: private field, public property and database column.
        private int m_mv_y1;

        [Column]
        public int mv_y1
        {
            get
            {
                return m_mv_y1;
            }
            set
            {
                if (m_mv_y1 != value)
                {
                    NotifyPropertyChanging("mv_y1");
                    m_mv_y1 = value;
                    NotifyPropertyChanged("mv_y1");
                }
            }
        }

        // Define item x2: private field, public property and database column.
        private int m_mv_y2;

        [Column]
        public int mv_y2
        {
            get
            {
                return m_mv_y2;
            }
            set
            {
                if (m_mv_y2 != value)
                {
                    NotifyPropertyChanging("mv_y2");
                    m_mv_y2 = value;
                    NotifyPropertyChanged("mv_y2");
                }
            }
        }

        // Version column aids update performance.
        [Column(IsVersion = true)]
        private Binary _version;

        // Foreign Key column for the associated Game ID value
        private int m_gm_id;

        [Column]
        public int gm_id
        {
            get
            {
                return m_gm_id;
            }
            set
            {
                if (m_gm_id != value)
                {
                    NotifyPropertyChanging("gm_id");
                    m_gm_id = value;
                    NotifyPropertyChanged("gm_id");
                }
            }
        }

        // Entity reference, to identify the Game "storage" table
        private EntityRef<Game> m_game;

        // Association, to describe the relationship between this key and that "storage" table
        [Association(Storage = "m_game", ThisKey = "gm_id", OtherKey = "gm_id", IsForeignKey = true)]
        public Game game
        {
            get { return m_game.Entity; }
            set
            {
                NotifyPropertyChanging("game");
                m_game.Entity = value;

                if (value != null)
                {
                    m_gm_id = value.gm_id;
                }

                NotifyPropertyChanging("game");
            }
        }

        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged;

        // Used to notify the page that a data context property changed
        private void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }

        #endregion

        #region INotifyPropertyChanging Members

        public event PropertyChangingEventHandler PropertyChanging;

        // Used to notify the data context that a data context property is about to change
        private void NotifyPropertyChanging(string propertyName)
        {
            if (PropertyChanging != null)
            {
                PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
            }
        }

        #endregion
    }
}
